package gov.nhso.prov.acc.apps.acc6;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.core.ConfigBundle;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

@SuppressWarnings({"rawtypes","deprecation"})
public class ACC60103 extends Utility{
	public String url = "/apps/acc6/ACC60103A.jsp";
	public ACC60103(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.init(request);
		} else if (job.equals("search")){
			this.search(request);
		} else if (job.equals("export")){
			this.export(request, response);
		}
	}
	private String init(HttpServletRequest request){
		this.initPage(request);
		return this.url;
	}
	private String search(HttpServletRequest request){
 		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String provinceId = this.getAttr(request, "provinceId");
			PageBean pageBean = new PageBean();
			StringBuffer sb = new StringBuffer();
			Map map = new HashMap();
			this.beanUtil().copyRequest2Bean(request, pageBean);
			Acc6TransactionBean transactionBean = new Acc6TransactionBean();
			if (pageBean.getOrderField().equals("UPDATED_DATE")) {
				pageBean.setOrderField("INCOME_DATE");
			}
			if(!dao.chkData(startDate).equals("")){
				if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
					startDate = this.dateUtil().changeDateToDB(startDate);
					sb.append(" AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDate);
				} else {
					startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
					sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= " + startDate);
				}
			}			
			if(!dao.chkData(endDate).equals("")){
				if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
					endDate = this.dateUtil().changeDateToDB(endDate);
					sb.append(" AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDate);
				} else {
					endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
					sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= " + endDate);
					
				}
			}
			transactionBean.setTransactionType("S");
			if (provinceId.equals("all")){
				if (this.user(request).getAreaLevel().equals("2")) {
					sb.append("AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+this.user(request).getAreaCode()+"') ");
				}
			} else {
				transactionBean.setProvinceId(provinceId);
			}
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			pageBean = dao.selectPage(transactionBean, xcols, sb.toString(), pageBean);
			
			sb.setLength(0);
			sb.append("SELECT SUM(INCOME_AMOUNT) AS SUM_INCOME_AMOUNT ");
			sb.append("FROM ACC6_TRANSACTION ");
			if (provinceId.equals("all")){
				if (this.user(request).getAreaLevel().equals("2")) {
					sb.append("WHERE PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+this.user(request).getAreaCode()+"') AND TRANSACTION_TYPE = 'S' ");
				} else {
					sb.append("WHERE TRANSACTION_TYPE = 'S' ");
				}
			} else {
				//sb.append("WHERE PROVINCE_ID = '" + provinceId + "' AND TRANSACTION_TYPE = 'S' ");
				if (provinceId.equals("")){
					sb.append("WHERE TRANSACTION_TYPE = 'S' ");
				} else {
					sb.append("WHERE PROVINCE_ID = '" + provinceId + "' AND TRANSACTION_TYPE = 'S' ");
				}
			}
			
			if(!dao.chkData(startDate).equals("")){
				if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
					sb.append(" AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDate);
				} else {
					sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= " + startDate);
				}
			}			
			if(!dao.chkData(endDate).equals("")){
				if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
					sb.append(" AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDate);
				} else {
					sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= " + endDate);
					
				}
			}
			map = dao.findDataSql(sb.toString());
			request.setAttribute("sumAmount", map.get("SUM_INCOME_AMOUNT"));
			request.setAttribute("pageBean", pageBean);
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private void export(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String provinceId = this.getAttr(request, "provinceId");
			PageBean pageBean = new PageBean();
			StringBuffer sb = new StringBuffer();
			Map map = new HashMap();
			List lsData = new ArrayList();
			this.beanUtil().copyRequest2Bean(request, pageBean);
			Acc6TransactionBean transactionBean = new Acc6TransactionBean();
			if(!dao.chkData(startDate).equals("")){
				if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
					startDate = this.dateUtil().changeDateToDB(startDate);
					sb.append(" AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDate);
				} else {
					startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
					sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= " + startDate);
				}
			}			
			if(!dao.chkData(endDate).equals("")){
				if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
					endDate = this.dateUtil().changeDateToDB(endDate);
					sb.append(" AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDate);
				} else {
					endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
					sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= " + endDate);
					
				}
			}
			transactionBean.setTransactionType("S");
			if (provinceId.equals("all")){
				if (this.user(request).getAreaLevel().equals("2")) {
					sb.append("AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+this.user(request).getAreaCode()+"') ");
				}
			} else {
				transactionBean.setProvinceId(provinceId);
			}
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			lsData = dao.selectList(transactionBean, xcols, sb.toString());
			
			sb.setLength(0);
			sb.append("SELECT SUM(INCOME_AMOUNT) AS SUM_INCOME_AMOUNT ");
			sb.append("FROM ACC6_TRANSACTION ");
			if (provinceId.equals("all")){
				if (this.user(request).getAreaLevel().equals("2")) {
					sb.append("WHERE PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+this.user(request).getAreaCode()+"') AND TRANSACTION_TYPE = 'S' ");
				} else {
					sb.append("WHERE TRANSACTION_TYPE = 'S' ");
				}
			} else {
				if (provinceId.equals("")){
					sb.append("WHERE TRANSACTION_TYPE = 'S' ");
				} else {
					sb.append("WHERE PROVINCE_ID = '" + provinceId + "' AND TRANSACTION_TYPE = 'S' ");
				}
			}
			
			if(!dao.chkData(startDate).equals("")){
				if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
					sb.append(" AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDate);
				} else {
					sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= " + startDate);
				}
			}			
			if(!dao.chkData(endDate).equals("")){
				if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
					sb.append(" AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDate);
				} else {
					sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= " + endDate);
				}
			}
			map = dao.findDataSql(sb.toString());

			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\"sapData.xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("sapData");
			HSSFCellStyle cellStyle = wb.createCellStyle();
			HSSFCellStyle cellAlignRigth = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellAlignRigth.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			HSSFRow row = sheet.createRow((short)0);
			HSSFCell cellRefNo = row.createCell(0);
			HSSFCell cellBudgetYear = row.createCell(1);
			HSSFCell cellIncomeDate = row.createCell(2);
			HSSFCell cellProvinceName = row.createCell(3);
			HSSFCell cellAllocate = row.createCell(4);
			HSSFCell cellAmount = row.createCell(5);
			HSSFCell cellSAPDocNo = row.createCell(6);
			
			sheet.setDefaultColumnWidth((short) 35);

			cellAllocate.setCellStyle(cellAlignRigth);
			cellAmount.setCellStyle(cellAlignRigth);
			String sumIncomeAmount = dao.chkData(map.get("SUM_INCOME_AMOUNT"));
			if (sumIncomeAmount.equals("")) {
				sumIncomeAmount = "0";
			}
			cellRefNo.setCellValue(" ");
			cellBudgetYear.setCellValue(" ");
			cellIncomeDate.setCellValue(" ");
			cellProvinceName.setCellValue(" ");
			cellAllocate.setCellValue("ยอดรวมทั้งหมด");
			cellAmount.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(sumIncomeAmount), 2));
			cellSAPDocNo.setCellValue(" ");
			
			row = sheet.createRow((short)1);

			cellRefNo = row.createCell(0);
			cellBudgetYear = row.createCell(1);
			cellIncomeDate = row.createCell(2);
			cellProvinceName = row.createCell(3);
			cellAllocate = row.createCell(4);
			cellAmount = row.createCell(5);
			cellSAPDocNo = row.createCell(6);
			
			cellRefNo.setCellStyle(cellStyle);
			cellBudgetYear.setCellStyle(cellStyle);
			cellIncomeDate.setCellStyle(cellStyle);
			cellProvinceName.setCellStyle(cellStyle);
			cellAllocate.setCellStyle(cellStyle);
			cellAmount.setCellStyle(cellStyle);
			cellSAPDocNo.setCellStyle(cellStyle);
			
			cellRefNo.setCellValue("เลขที่เอกสาร");
			cellBudgetYear.setCellValue("ปีงบประมาณ");
			cellIncomeDate.setCellValue("วันที่รับโอน");
			cellProvinceName.setCellValue("สปสช.สาขาจังหวัด");
			cellAllocate.setCellValue("กองทุนย่อยเฉพาะด้าน");
			cellAmount.setCellValue("จำนวนเงิน");
			cellSAPDocNo.setCellValue("เลขที่เอกสารอ้างอิงจาก sap");
			
			for (int i = 0 ; i < lsData.size() ; i++) {
				row = sheet.createRow((short)i+2);
				cellRefNo = row.createCell(0);
				cellBudgetYear = row.createCell(1);
				cellIncomeDate = row.createCell(2);
				cellProvinceName = row.createCell(3);
				cellAllocate = row.createCell(4);
				cellAmount = row.createCell(5);
				cellSAPDocNo = row.createCell(6);
				
				cellRefNo.setCellStyle(cellStyle);
				cellBudgetYear.setCellStyle(cellStyle);
				cellIncomeDate.setCellStyle(cellStyle);
				cellAmount.setCellStyle(cellAlignRigth);
				cellSAPDocNo.setCellStyle(cellStyle);
				
				Acc6TransactionBean bean = (Acc6TransactionBean) lsData.get(i);
				cellRefNo.setCellValue(bean.getRefNo());
				cellBudgetYear.setCellValue(bean.getBudgetYear());
				cellIncomeDate.setCellValue(this.dateUtil().changeDateFromDB(bean.getIncomeDate(), "/"));
				cellProvinceName.setCellValue(this.dataUtil(dao).getProvinceName(bean.getProvinceId()));
				if (bean.getAllocateCode() == null || bean.getAllocateCode().equals("")) {
					cellAllocate.setCellValue("");
				} else {
					cellAllocate.setCellValue(bean.getAllocateCode() + " - " + this.dataUtil(dao).getAllocateById(dao.chkData(bean.getAllocateCode())));
				}
				cellAmount.setCellValue(this.digitUtil().numberFormat("#,##0.00", dao.chkData(bean.getIncomeAmount())));
				cellSAPDocNo.setCellValue(bean.getTransactionTimestamp());
			}
			
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			this.message(request).clearMsg();
			this.url = "";
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
